package com.shh.poi;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.BaseFormulaEvaluator;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.BaseXSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.*;

/**
 * 读取excel
 */
public class ExcelReadTest {
    @Test
    public void test1() throws IOException {
        // 文件输入流载入工作表
        try (final InputStream fileInputStream
                     = new FileInputStream("src/main/resources/a.xlsx");
             final OutputStream fileOutputStream
                     = new FileOutputStream("src/main/resources/a2.xlsx");
        ) {
            Workbook workbook = new XSSFWorkbook(fileInputStream);
            final Sheet sheet = workbook.getSheetAt(0);
            final Row row = sheet.getRow(0);
            final CellStyle cellStyle = workbook.createCellStyle();
            final Font font = workbook.createFont();
            font.setColor(HSSFColor.RED.index);
            cellStyle.setFont(font);
            //row.setRowStyle(cellStyle);
            final short lastCellNum = row.getLastCellNum();
            for (short cellNum = 0; cellNum < lastCellNum; cellNum++) {
                final Cell cell = row.getCell(cellNum);
                cell.setCellStyle(cellStyle);
            }
            //final Cell cell = row.getCell(0);
            //System.out.println(cell.getStringCellValue());
            workbook.write(fileOutputStream);
        }


    }


    @Test
    public void test2() throws IOException {
        // 文件输入流载入工作表
        try (final InputStream fileInputStream
                     = new FileInputStream("src/main/resources/公式.xls");
        ) {
            Workbook workbook = new HSSFWorkbook(fileInputStream);
            final Sheet sheet = workbook.getSheetAt(0);
            final Row row = sheet.getRow(4);
            final Cell cell = row.getCell(0);
            //解析此处的公式
            if (cell != null) {
                // Cannot get a STRING value from a NUMERIC formula cell
                //System.out.println(cell.getStringCellValue());

                final int cellType = cell.getCellType();
                switch (cellType) {
                    case Cell.CELL_TYPE_FORMULA:
                        // 获取公式的字符串表示
                        final String cellFormula = cell.getCellFormula();
                        System.out.println(cellFormula);
                        FormulaEvaluator evaluator
                                = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
                        final CellValue evaluate = evaluator.evaluate(cell);
                        //final String s = String.valueOf(evaluate.getNumberValue());
                        final String s = evaluate.formatAsString();
                        System.out.println(evaluate);
                        System.out.println(s);
                        break;

                }

            }

        }


    }
}
